using System;
using System.Data;
using System.Data.SQLite;

// ACME oracle username: ops$sbass6  /  password: ProjectPhase3

// Lightweight project - to get prompted for input in sql statements, use &variableName. For example: select * from department where dnumber = &dnumber (not useful for heavyweight project)

// Heavyweight project - store createTables.sql on ACME and execute it by calling "START createTables"
// createTables.sql should create all tables and constraints
// dropTables.sql should drop all tables and constraints
// insertTables.sql should insert the predefined information

/// <summary>
///  or is the interface to the database for the entire application. Any requests for a query are handed off to  or.
/// </summary>
public class  dbAccessor
{
    SQLiteConnection sql_con;

    /// <summary>
    /// Default constructor - connects to local SQLite database
    /// </summary>
    public  dbAccessor()
    {
        string connectionString = "Data Source=Project3DB.s3DB;";
        sql_con = new SQLiteConnection(connectionString);
        createTables();
    }

    public SQLiteCommand getCommand()
    {
        return sql_con.CreateCommand();
    }

    /// <summary>
    /// Creates the tables needed for a festival planning database.
    /// </summary>
    public void createTables()
    {
        SQLiteCommand sql_cmd = getCommand();

        // create db_user table
        sql_cmd.CommandText = "CREATE TABLE db_user" +
                              "(username VARCHAR(16) NOT NULL,"+
                              "password  VARCHAR(16) NOT NULL,"+
                              "PRIMARY KEY (username))";
        executeNonQuery(sql_cmd);

        // create category table
        sql_cmd.CommandText = "CREATE TABLE category" +
                              "(id         INT NOT NULL," +
                              "fee         INT," +
                              "description VARCHAR(25)," +
                              "UNIQUE (id, description)," +
                              "PRIMARY KEY (id));";
        executeNonQuery(sql_cmd);

        // create vendor table
        sql_cmd.CommandText = "CREATE TABLE vendor" +
                              "(vendor_name       VARCHAR(25)," +
                              "cctype             VARCHAR(4)," +
                              "credit_card_number INT," +
                              "license_number     INT NOT NULL," +
                              "category           INT," +
                              "telephone_number   VARCHAR(10)," +
                              "PRIMARY KEY (license_number)," +
                              "FOREIGN KEY (category) REFERENCES category(id)" +
                                                      "ON DELETE SET NULL ON UPDATE CASCADE);";
        executeNonQuery(sql_cmd);

        // create sponsor table
        sql_cmd.CommandText = "CREATE TABLE sponsor" +
                              "(organization       VARCHAR(25) NOT NULL," +
                              "contact_name        VARCHAR(25)," +
                              "telephone_number    VARCHAR(10)," +
                              "PRIMARY KEY (organization));";
        executeNonQuery(sql_cmd);

        // create fine_artist table
        sql_cmd.CommandText = "CREATE TABLE fine_artist" +
                              "(name             VARCHAR(25) NOT NULL," +
                              "telephone_number  VARCHAR(14) NOT NULL," +
                              "artist_category   VARCHAR(25)," +
                              "gallery           VARCHAR(25)," +
                              "PRIMARY KEY (name, telephone_number));";
        executeNonQuery(sql_cmd);

        // create musician table
        sql_cmd.CommandText = "CREATE TABLE musician" +
                              "(musician_name      VARCHAR(25) NOT NULL," +
                              "performance_fee     INT," +
                              "genre               VARCHAR(25)," +
                              "telephone_number    VARCHAR(14)," +
                              "PRIMARY KEY (musician_name));";
        executeNonQuery(sql_cmd);

        // create festival table
        sql_cmd.CommandText = "CREATE TABLE festival" +
                              "(name               VARCHAR(25)," +
                              "number_of_artists   INT," +
                              "net_profit          INT," +
                              "cost                INT," +
                              "funds_raised        INT," +
                              "start_date          DATETIME," +
                              "end_date            DATETIME," +
                              "duration            INT," +
                              "id                  INT NOT NULL," +
                              "location            VARCHAR(25)," +
                              "festival_type       VARCHAR(5)," +
                              "PRIMARY KEY (id));";
        executeNonQuery(sql_cmd);

        // create music_festival_has_stage table
        sql_cmd.CommandText = "CREATE TABLE music_festival_has_stage" +
                              "(festival_id    INT NOT NULL," +
                              "stage_name      VARCHAR(25)," +
                              "PRIMARY KEY (festival_id, stage_name)," +
                              "FOREIGN KEY (festival_id) REFERENCES festival(id) " +
                                                         "ON DELETE CASCADE ON UPDATE CASCADE);";
        executeNonQuery(sql_cmd);

        // create festival_has_fine_artist table
        sql_cmd.CommandText = "CREATE TABLE festival_has_fine_artist" +
                              "(festival_id    INT NOT NULL," +
                              "fa_name         VARCHAR(25) NOT NULL," +
                              "fa_phone        VARCHAR(14) NOT NULL," +
                              "PRIMARY KEY (festival_id, fa_name, fa_phone)," +
                              "FOREIGN KEY (festival_id) REFERENCES festival(id) " +
                                                         "ON DELETE CASCADE ON UPDATE CASCADE," +
                              "FOREIGN KEY (fa_name) REFERENCES fine_artist(name) " +
                                                         "ON DELETE CASCADE ON UPDATE CASCADE," +
                              "FOREIGN KEY (fa_phone) REFERENCES fine_artist(telephone_number) " +
                                                         "ON DELETE CASCADE ON UPDATE CASCADE);";
        executeNonQuery(sql_cmd);

        // create festival_has_sponsor table
        sql_cmd.CommandText = "CREATE TABLE festival_has_sponsor" +
                              "(festival_id    INT NOT NULL," +
                              "sponsor_org     VARCHAR(25) NOT NULL," +
                              "donation_amt    INT NOT NULL," +
                              "PRIMARY KEY (festival_id, sponsor_org)," +
                              "FOREIGN KEY (festival_id) REFERENCES festival(id) " +
                                                         "ON DELETE CASCADE ON UPDATE CASCADE," +
                              "FOREIGN KEY (sponsor_org) REFERENCES sponsor(organization) " +
                                                         "ON DELETE CASCADE ON UPDATE CASCADE);";
        executeNonQuery(sql_cmd);

        // create musician_plays table
        sql_cmd.CommandText = "CREATE TABLE musician_plays" +
                              "(festival_id    INT NOT NULL," +
                              "musician_name   VARCHAR(25) NOT NULL," +
                              "stage_name      VARCHAR(25) NOT NULL," +
                              "date            DATE NOT NULL," +
                              "time            INT NOT NULL," +
                              "PRIMARY KEY (festival_id, stage_name, date, time)," +
                              "FOREIGN KEY (festival_id, stage_name) REFERENCES music_festival_has_stage(festival_id, stage_name)" +
                                                                     "ON DELETE CASCADE ON UPDATE CASCADE," +
                              "FOREIGN KEY (musician_name) REFERENCES musician(name)" +
                                                                     "ON DELETE CASCADE ON UPDATE CASCADE);";
        executeNonQuery(sql_cmd);

        // create festival_has_vendor table
        sql_cmd.CommandText = "CREATE TABLE festival_has_vendor" +
                              "(festival_id        INT NOT NULL," +
                              "vendor_license_num  INT NOT NULL," +
                              "PRIMARY KEY (festival_id, vendor_license_num)," +
                              "FOREIGN KEY (festival_id) REFERENCES festival(id)" +
                                                         "ON DELETE CASCADE ON UPDATE CASCADE," +
                              "FOREIGN KEY (vendor_license_num) REFERENCES vendor(license_number)" +
                                                                "ON DELETE CASCADE ON UPDATE CASCADE);";
        executeNonQuery(sql_cmd);
    }

    /// <summary>
    /// Drops all tables associated with festival planning database.
    /// </summary>
    public void dropAllTables()
    {
        SQLiteCommand sql_cmd = getCommand();

        // drop db_user table
        sql_cmd.CommandText = "DROP TABLE db_user;";
        executeNonQuery(sql_cmd);

        // drop category table
        sql_cmd.CommandText = "DROP TABLE category;";
        executeNonQuery(sql_cmd);

        // drop vendor table
        sql_cmd.CommandText = "DROP TABLE vendor;";
        executeNonQuery(sql_cmd);

        // drop sponsor table
        sql_cmd.CommandText = "DROP TABLE sponsor;";
        executeNonQuery(sql_cmd);

        // drop fine_artist table
        sql_cmd.CommandText = "DROP TABLE fine_artist;";
        executeNonQuery(sql_cmd);

        // drop musician table
        sql_cmd.CommandText = "DROP TABLE musician;";
        executeNonQuery(sql_cmd);

        // drop festival table
        sql_cmd.CommandText = "DROP TABLE festival;";
        executeNonQuery(sql_cmd);

        // drop music_festival_has_stage table
        sql_cmd.CommandText = "DROP TABLE music_festival_has_stage;";
        executeNonQuery(sql_cmd);

        // drop festival_has_fine_artist table
        sql_cmd.CommandText = "DROP TABLE festival_has_fine_artist;";
        executeNonQuery(sql_cmd);

        // drop festival_has_sponsor table
        sql_cmd.CommandText = "DROP TABLE festival_has_sponsor;";
        executeNonQuery(sql_cmd);

        // drop musician_plays table
        sql_cmd.CommandText = "DROP TABLE musician_plays;";
        executeNonQuery(sql_cmd);

        // drop festival_has_vendor table
        sql_cmd.CommandText = "DROP TABLE festival_has_vendor;";
        executeNonQuery(sql_cmd);
    }


    /// <summary>
    /// Populates the database with prescribed values from the assignment.
    /// </summary>
    public void populateDatabase()
    {
        //mafuser insertion 
        //(for loop used strictly to enforce scope rules for variable names)
        for (int i = 0; i < 1; i++)
        {
            SQLiteCommand nonQuery = getCommand();
            nonQuery.CommandText = "INSERT INTO db_user " +
                                   "VALUES (@user, @pass);";
            SQLiteParameter param = new SQLiteParameter("@user", "buddy guy");
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@pass", "theblues");
            nonQuery.Parameters.Add(param);

            executeNonQuery(nonQuery);
        }

        //festival arrays
        int[] id = { 1245, 1350 };
        string[] festName = { "2010 blues festival", "2010 picasso festival" };
        string[] location = { "grant park", "navy pier" };
        string[] start = { "06/11/2010", "06/11/2010" };
        string[] end = { "06/13/2010", "06/12/2010" };
        string[] type = { "Music", "Art" };

        //festival insertion
        for (int i = 0; i < 2; i++)
        {
            SQLiteCommand nonQuery = getCommand();
            nonQuery.CommandText = "INSERT INTO festival(id, name, start_date, end_date, location, festival_type) " +
                                   "VALUES (@Id, @Name, @Start, @End, @Location, @Type);";
            nonQuery.Parameters.Add("@Id", DbType.Int32);
            nonQuery.Parameters["@Id"].Value = id[i];
            nonQuery.Parameters.Add("@Name", DbType.String);
            nonQuery.Parameters["@Name"].Value = festName[i];
            SQLiteParameter param = new SQLiteParameter("@Start", DateTime.Parse(start[i]));
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@End", DateTime.Parse(end[i]));
            nonQuery.Parameters.Add(param);
            nonQuery.Parameters.Add("@Location", DbType.String);
            nonQuery.Parameters["@Location"].Value = location[i];
            nonQuery.Parameters.Add("@Type", DbType.String);
            nonQuery.Parameters["@Type"].Value = type[i];
            executeNonQuery(nonQuery);
        }

        //vendorcategory arrays
        int[] catNumber = { 1, 2, 3 };
        string[] catDesc = { "food/beverage", "beverage", "crafts" };
        int[] catFee = { 1000, 800, 500 };

        //vendorcategory insertion
        for (int i = 0; i < 3; i++)
        {
            SQLiteCommand nonQuery = getCommand();
            nonQuery.CommandText = "INSERT INTO category " +
                                   "VALUES (@id, @fee, @desc);";
            SQLiteParameter param = new SQLiteParameter("@id", catNumber[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@fee", catFee[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@desc", catDesc[i]);
            nonQuery.Parameters.Add(param);
            
            executeNonQuery(nonQuery);
        }

        //vendor arrays
        int[] license = { 7722166, 7722167, 7722168, 7722169, 7722170, 7722171, 7722172 };
        string[] vendorName = { "fatt matts ribs", "the varsity", "ribs are us", "northside tavern", "sweet water brewery", "vintage tee shirts", "jewlery plus" };
        string[] vendorPhone = { "(404)-332-1111", "(404)-332-0000", "(404)-332-2222", "(404)-332-3333", "(404)-332-4444", "(404)-332-5555", "(404)-332-6666" };
        int[] ccn = { 123456789, 123456788, 123456700, 123456701, 123456702, 123456703, 123456704 };
        string[] cctype = { "VISA", "VISA", "VISA", "VISA", "AMEX", "VISA", "VISA" };
        int[] category = { 1, 1, 1, 2, 2, 3, 3 };

        //vendor insertion
        for (int i = 0; i < 7; i++)
        {
            SQLiteCommand nonQuery = getCommand();
            nonQuery.CommandText = "INSERT INTO vendor " +
                                    "VALUES (@name, @cctype, @card_num, @lic_num, @cat, @phone);";
            SQLiteParameter param = new SQLiteParameter("@name", vendorName[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@cctype", cctype[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@card_num", ccn[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@lic_num", license[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@cat", category[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@phone", vendorPhone[i]);
            nonQuery.Parameters.Add(param);

            executeNonQuery(nonQuery);
        }

        //festivalvendor arrays
        int[] festivalVendorFestivalID = { 1245, 1245, 1245, 1245, 1245, 1245, 1350, 1350, 1350 };
        int[] festivalVendorLicenseNumber = { 7722166, 7722167, 7722168, 7722169, 7722171, 7722172, 7722168, 7722169, 7722170 };

        //festivalvendor insertion
        for (int i = 0; i < 9; i++)
        {
            SQLiteCommand nonQuery = getCommand();
            nonQuery.CommandText = "INSERT INTO festival_has_vendor " +
                           "VALUES (@id, @lic_num);";
            SQLiteParameter param = new SQLiteParameter("@id", festivalVendorFestivalID[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@lic_num", festivalVendorLicenseNumber[i]);
            nonQuery.Parameters.Add(param);

            executeNonQuery(nonQuery);
        }

        //sponsor arrays
        string[] org = { "bank of america", "walmart", "cisco", "target", "yahoo" };
        string[] contact = { "jeff beck", "joe bonamassa", "robben ford", "derek trucks", "larry carlton" };
        string[] contactPhone = { "(770)-222-1111", "(770)222-2222", "(770)-222-3333", "(770)-222-4444", "(770)222-5555" };

        //sponsor insertion
        for (int i = 0; i < 5; i++)
        {
            SQLiteCommand nonQuery2 = getCommand();
            nonQuery2.CommandText = "INSERT INTO sponsor " +
                                    "VALUES (@Org, @Contact, @Phone);";
            SQLiteParameter param1 = new SQLiteParameter("@Org", org[i]);
            nonQuery2.Parameters.Add(param1);
            param1 = new SQLiteParameter("@Contact", contact[i]);
            nonQuery2.Parameters.Add(param1);
            param1 = new SQLiteParameter("@Phone", contactPhone[i]);
            nonQuery2.Parameters.Add(param1);
            executeNonQuery(nonQuery2);
        }

        //festivalsponsor arrays
        int[] festivalSponsorFestivalID = {1245, 1245, 1245, 1245, 1245, 1350, 1350};
        string[] festivalSponsorOrganization = {"bank of america", "walmart", "cisco", "target", "yahoo", "target", "yahoo"};
        int[] festivalSponsorAmount = {20000, 30000, 5000, 2000, 3000, 5000, 4000};

        //festivalsponsor insertion
        for (int i = 0; i < 7; i++)
        {
            SQLiteCommand nonQuery = getCommand();
            nonQuery.CommandText = "INSERT INTO festival_has_sponsor " +
                           "VALUES (@id, @sponsor, @donation);";
            SQLiteParameter param = new SQLiteParameter("@id", festivalSponsorFestivalID[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@sponsor", festivalSponsorOrganization[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@donation", festivalSponsorAmount[i]);
            nonQuery.Parameters.Add(param);

            executeNonQuery(nonQuery);
        }

        //fineartist & artfineartist arrays
        int[] artFineArtistFestivalID = {1350, 1350, 1350, 1350, 1350, 1350, 1350, 1350};
        string[] artFineArtistName = {"anne smith", "peter max", "leonardo", "mary jones", "jeff brown", "david bowie", "susan crowe",  "robert plant"};
        string[] artFineArtistPhone = {"(666) 111-1000", "(666) 111-1001", "(666) 111-1002", "(666) 111-1003", "(666) 111-1004", "(666) 111-1005", "(666) 111-1006", "(666) 111-1007"};
        string[] artistCat = { "painting", "painting", "painting", "pottery", "pottery", "silversmith", "sculptor", "woodturner" };
        string[] artistGallery = { "chicago, il", "chicago, il", "chicago, il", "atlanta, ga", "atlanta, ga", "miami, fl", "new york, ny", "ashville, nc" };

        //fineartist & artfineartist insertion
        for (int i = 0; i < 8; i++)
        {
            SQLiteCommand nonQuery = getCommand();
            nonQuery.CommandText = "INSERT INTO fine_artist " +
                           "VALUES (@name, @phone, @cat, @gallery);";
            SQLiteParameter param = new SQLiteParameter("@name", artFineArtistName[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@phone", artFineArtistPhone[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@cat", artistCat[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@gallery", artistGallery[i]);
            nonQuery.Parameters.Add(param);

            executeNonQuery(nonQuery);

            nonQuery = getCommand();
            nonQuery.CommandText = "INSERT INTO festival_has_fine_artist " +
                           "VALUES (@id, @name, @phone);";
            param = new SQLiteParameter("@id", artFineArtistFestivalID[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@name", artFineArtistName[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@phone", artFineArtistPhone[i]);
            nonQuery.Parameters.Add(param);

            executeNonQuery(nonQuery);
        }

        //stage arrays
        int[] stageFestivalID = { 1245, 1245, 1245 };
        string[] stageName = { "main", "road house", "back porch" };

        //stage insertion
        for (int i = 0; i < 3; i++)
        {
            SQLiteCommand nonQuery = getCommand();
            nonQuery.CommandText = "INSERT INTO music_festival_has_stage " +
                           "VALUES (@id, @name);";
            SQLiteParameter param = new SQLiteParameter("@id", stageFestivalID[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@name", stageName[i]);
            nonQuery.Parameters.Add(param);

            executeNonQuery(nonQuery);
        }

        //musician, musicmusician, and stagemusicianschedule arrays
        int[] scheduleFestID = new int[20];
        for (int i = 0; i < 20; i++)
        {
            scheduleFestID[i] = 1245;
        }

        string[] scheduleStageName = new string[20];
        scheduleStageName[0] = "road house";
        for (int i = 1; i < 15; i++)
        {
            scheduleStageName[i] = "main";
        }
        for (int i = 15; i < 20; i++)
        {
            scheduleStageName[i] = "road house";
        }

        string[] scheduleSDate = new string[20];
        for (int i = 0; i < 15; i++)
        {
            scheduleSDate[i] = formatGenericDate("06/11/2010");
        }
        for (int i = 15; i < 20; i++)
        {
            scheduleSDate[i] = formatGenericDate("06/12/2010");
        }

        int[] scheduleSTime = new int[20];
        scheduleSTime[0] = 10;

        for (int i = 1; i < 15; i++)
        {
            scheduleSTime[i] = i + 9;
        }
        for (int i = 15; i < 20; i++)
        {
            scheduleSTime[i] = i - 5;
        }

        string[] scheduleMusicianName = new string[20];
        scheduleMusicianName[0] = "govt mule"; scheduleMusicianName[1] = "tbone walker"; scheduleMusicianName[2] = "blind lemon"; scheduleMusicianName[3] = "freddie king"; scheduleMusicianName[4] = "albert king"; scheduleMusicianName[5] = "john lee"; scheduleMusicianName[6] = "magic slim"; scheduleMusicianName[7] = "willie dixon"; scheduleMusicianName[8] = "paul butterfield"; scheduleMusicianName[9] = "koko taylor"; scheduleMusicianName[10] = "magic sam"; scheduleMusicianName[11] = "otis rush"; scheduleMusicianName[12] = "howlin wolf"; scheduleMusicianName[13] = "muddy waters"; scheduleMusicianName[14] = "buddy guy"; scheduleMusicianName[15] = "kim wilson"; scheduleMusicianName[16] = "tommy castro"; scheduleMusicianName[17] = "robert cray"; scheduleMusicianName[18] = "eric clapton"; scheduleMusicianName[19] = "john mayall";

        string[] musicianPhone = { "777-111-1111", "777-111-1122", "777-111-1133", "777-111-1144", "777-111-1155", "777-111-1166", "777-111-1177", "777-111-1188", "777-111-1199", "777-111-2200", "777-111-2211", "777-111-2212", "777-111-2213", "777-111-2214", "777-111-2215", "888-111-1122", "888-111-1133", "888-111-1144", "888-111-1155", "888-111-1166" };

        int[] musicFee = { 15000, 5000, 5000, 10000, 10000, 10000, 10000, 12000, 12000, 10000, 5000, 10000, 10000, 20000, 30000, 4000, 4000, 5000, 5000, 5000 };

        string[] genre = new string[20];
        genre[0] = "blues rock";
        for (int i = 1; i < 20; i++)
            genre[i] = "blues";

        //musician, musicmusician, and stagemusicianschedule insertion
        for (int i = 0; i < 20; i++)
        {
            SQLiteCommand nonQuery = getCommand();
            nonQuery.CommandText = "INSERT INTO musician " +
                           "VALUES (@name, @fee, @genre, @phone);";
            SQLiteParameter param = new SQLiteParameter("@name", scheduleMusicianName[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@fee", musicFee[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@genre", genre[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@phone", musicianPhone[i]);
            nonQuery.Parameters.Add(param);

            executeNonQuery(nonQuery);

            nonQuery = getCommand();
            nonQuery.CommandText = "INSERT INTO musician_plays " +
                           "VALUES (@id, @name, @stage, @date, @time);";
            param = new SQLiteParameter("@id", scheduleFestID[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@name", scheduleMusicianName[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@stage", scheduleStageName[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@date", scheduleSDate[i]);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@time", scheduleSTime[i]);
            nonQuery.Parameters.Add(param);

            executeNonQuery(nonQuery);
        }
    }





    /// <summary>
    /// Executes inserts, deletes, modifies, etc. Any statement that does not return a table.
    /// </summary>
    /// <param name="txtQuery">the non-query to be executed</param>
    /// <returns>boolean value indicating success or failure</returns>
    public bool executeNonQuery(SQLiteCommand sql_cmd)
    {
        sql_con.Open();
        try
        {   
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
            return true;
        }
        catch
        {
            sql_con.Close();
            return false;
        }
    }

    /// <summary>
    /// Executes a query that returns a datatable.
    /// </summary>
    /// <param name="sql_cmd">SQLiteCommand retrieved with sql_con.getCommand and filled with args</param>
    /// <returns>DataTable with results</returns>
    public DataTable executeQuery(SQLiteCommand sql_cmd)
    {
        sql_con.Open();
        DataTable dt = new DataTable();
        try
        {
            SQLiteDataReader reader = sql_cmd.ExecuteReader();
            dt.Load(reader);
            reader.Close();
        }
        catch
        {
            sql_con.Close();
        }
        sql_con.Close();
        return dt;
    }

    /// <summary>
    /// Takes a date in the form mm/dd/yyyy and returns a date in a valid database
    /// DateTime format.
    /// </summary>
    /// <param name="genericDate">date mm/dd/yyyy</param>
    /// <returns>database DateTime</returns>
    protected string formatGenericDate(string genericDate)
    {
        char[] date = genericDate.ToCharArray();

        char[] reformatted = new char[date.Length];

        reformatted[0] = date[date.Length - 4];
        reformatted[1] = date[date.Length - 3];
        reformatted[2] = date[date.Length - 2];
        reformatted[3] = date[date.Length - 1];
        reformatted[4] = '-';
        reformatted[5] = date[0];
        reformatted[6] = date[1];
        reformatted[7] = '-';
        reformatted[8] = date[3];
        reformatted[9] = date[4];

        string result = new string(reformatted);

        return result;
    }
}